package com.sqlcheck.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sqlcheck.entity.ScanResult;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface ScanResultRepository extends BaseMapper<ScanResult> {

        @Select("SELECT id, task_id, file_path, statement_number, statement_id, file_type, sql_content, sql_type, line_number, column_number, issue_type, severity, rule_id, rule_name, issue_description, suggestion, original_sql, suggested_sql, metadata AS metadataJson, analysis_engine, ai_optimized, ai_confidence_score, optimized_sql, optimization_explanation, rule_analysis_confirmed, ai_issue_description, ai_verification_details AS aiVerificationDetailsJson, created_at, deleted FROM scan_results WHERE task_id = #{taskId} AND deleted = 0 ORDER BY severity DESC, file_path ASC")
        List<ScanResult> findByTaskId(@Param("taskId") Long taskId);

        @Select("SELECT id, task_id, file_path, statement_number, statement_id, file_type, sql_content, sql_type, line_number, column_number, issue_type, severity, rule_id, rule_name, issue_description, suggestion, original_sql, suggested_sql, metadata AS metadataJson, analysis_engine, ai_optimized, ai_confidence_score, optimized_sql, optimization_explanation, rule_analysis_confirmed, ai_issue_description, ai_verification_details AS aiVerificationDetailsJson, created_at, deleted FROM scan_results WHERE task_id = #{taskId} AND severity = #{severity} AND deleted = 0")
        List<ScanResult> findByTaskIdAndSeverity(@Param("taskId") Long taskId,
                        @Param("severity") ScanResult.Severity severity);

        @Select("SELECT id, task_id, file_path, statement_number, statement_id, file_type, sql_content, sql_type, line_number, column_number, issue_type, severity, rule_id, rule_name, issue_description, suggestion, original_sql, suggested_sql, metadata AS metadataJson, analysis_engine, ai_optimized, ai_confidence_score, optimized_sql, optimization_explanation, rule_analysis_confirmed, ai_issue_description, ai_verification_details AS aiVerificationDetailsJson, created_at, deleted FROM scan_results WHERE task_id = #{taskId} AND issue_type = #{issueType} AND deleted = 0")
        List<ScanResult> findByTaskIdAndIssueType(@Param("taskId") Long taskId,
                        @Param("issueType") ScanResult.IssueType issueType);

        @Select("SELECT COUNT(*) FROM scan_results WHERE task_id = #{taskId} AND severity = #{severity} AND deleted = 0")
        int countByTaskIdAndSeverity(@Param("taskId") Long taskId, @Param("severity") ScanResult.Severity severity);

        @Update("UPDATE scan_results SET deleted = 1 WHERE task_id = #{taskId}")
        int softDeleteByTaskId(@Param("taskId") Long taskId);
}